﻿using API_test.Model;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using API_test.Common;
using System.Data;
using System.Data.SqlClient;

namespace API_test.DAL
{
    public class CustomerDAL
    {
        private Customer ToCustomer(DataRow row)
        {
            Customer cust = new Customer();
            cust.Id = (int)row["Id"];
            cust.Name = (string)row["Name"];
            cust.Sex = (string)row["Sex"];
            cust.Age = (int)row["Age"];
            cust.Department = (string)row["Department"];
            cust.BirthDay = (DateTime?)SqlHelper.FromDbValue(row["BirthDay"]);
            cust.HireDate = (DateTime?)SqlHelper.FromDbValue(row["HireDate"]);
            cust.TelNum = (string)row["TelNum"];
            cust.Address = (string)row["Address"];
            return cust;
        }


        #region 用户登录
        public int UserLogin(UserInfo user)
        {
            int i = (int)SqlHelper.ExecuteScalar("select count(*) from T_UserLogin where UserName=@UserName and PassWord=@PassWord",
                 new SqlParameter("@UserName", user.UserName),
                  new SqlParameter("@PassWord", user.PassWord));
            return i;
        }
        #endregion

        #region  增加
        public void Insert(Customer customer)
        {
            SqlHelper.ExecuteNonQuery(@"INSERT INTO [T_Customer]
                       ([Name],[Sex],[Age],[Department]
                       ,[BirthDay],[HireDate],[Address],[TelNum])                                 
                        VALUES
                       (@Name,@Sex,@Age,@Department,@BirthDay,@HireDate,@Address,@TelNum)",
                new SqlParameter("@Name", customer.Name),
                new SqlParameter("@Sex", customer.Sex),
                new SqlParameter("@Age", customer.Age),
                new SqlParameter("@Department", customer.Department),
                new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay)),
                new SqlParameter("@HireDate", SqlHelper.ToDbValue(customer.HireDate)),
                new SqlParameter("@Address", customer.Address),
                new SqlParameter("@TelNum", customer.TelNum));
        }
        #endregion

        #region  删除
        public void DeleteById(int id)
        {
            SqlHelper.ExecuteNonQuery("delete from T_Customer where Id=@Id",
                new SqlParameter("@Id", id));
        }
        #endregion

        #region 更改
        public void Update(Customer customer)
        {
            SqlHelper.ExecuteNonQuery(@"UPDATE [T_Customer]
                        SET [Name]=@Name
                           ,[Sex]=@Sex
                           ,[Age]=@Age
                           ,[Department]=@Department
                           ,[BirthDay] = @BirthDay
                           ,[HireDate]=@HireDate
                           ,[Address] = @Address
                           ,[TelNum] = @TelNum        
                            WHERE Id=@Id",
                new SqlParameter("@Name", customer.Name),
                new SqlParameter("@Sex", customer.Sex),
                new SqlParameter("@Age", customer.Age),
                new SqlParameter("@Department", customer.Department),
                new SqlParameter("@BirthDay", SqlHelper.ToDbValue(customer.BirthDay)),
                new SqlParameter("@HireDate", SqlHelper.ToDbValue(customer.HireDate)),
                new SqlParameter("@Address", customer.Address),
                new SqlParameter("@TelNum", customer.TelNum),
                new SqlParameter("@Id", customer.Id));
        }
        #endregion

        #region 查询
        public Customer[] GetDataById(int id)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where Id=@Id",
                new SqlParameter("@Id", id));
            Customer[] customers = new Customer[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else if (dt.Rows.Count > 1)
            {
                throw new Exception("严重错误，查出多条数据！");
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    customers[i] = ToCustomer(row);
                }
            }
            return customers;
        }
        public Customer[] GetDataBySex(string Sex)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where Sex=@Sex",
                new SqlParameter("@Sex", Sex));
            Customer[] customers = new Customer[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    customers[i] = ToCustomer(row);
                }
            }
            return customers;
        }
        public Customer[] GetDataByAge(int Age)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where Age=@Age",
                new SqlParameter("@Age", Age));
            Customer[] customers = new Customer[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    customers[i] = ToCustomer(row);
                }
            }
            return customers;
        }
        public Customer[] GetDataByDepartment(string Department)
        {
            DataTable dt = SqlHelper.ExecuteDataTable("select * from T_Customer where Department=@Department", new SqlParameter("@Department", Department));
            Customer[] customers = new Customer[dt.Rows.Count];
            if (dt.Rows.Count <= 0)
            {
                return null;
            }
            else
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow row = dt.Rows[i];
                    customers[i] = ToCustomer(row);
                }
            }
            return customers;
        }
        public Customer[] GetAllData()
        {

            DataTable table = SqlHelper.ExecuteDataTable("select * from T_Customer");
            Customer[] customers = new Customer[table.Rows.Count];
            for (int i = 0; i < table.Rows.Count; i++)
            {
                DataRow row = table.Rows[i];
                customers[i] = ToCustomer(row);
            }
            return customers;
        }
        #endregion  
    }
}
